﻿using System;
using System.Data.EntityClient;
using System.Linq;
using iss.sg4210.se18.team6s.jass.DAL.DataAccess.Interfaces;
using System.Data.Objects;
using System.Text;

namespace iss.sg4210.se18.team6s.jass.DAL.DataAccess.MSSQL
{
    public class SearchDAO : ObjectDAO, ISearch
    {
        public IQueryable<Advertisement> GetAllSearchResults()
        {
            IQueryable<Advertisement> searchResults = jassEntities.Advertisements.Where(m => DateTime.Compare(m.StartDate, DateTime.Now) >= 0 && !m.IsDeleted && !m.IsWithDrawn && !m.IsFulFilled);

            return from adv in searchResults orderby adv.AdvertisementType.Cost descending select adv;
        }

        public IQueryable<Advertisement> GetSearchResults(MetaData.SearchModel searchModel)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT VALUE o FROM ENTITIES.Advertisements AS o WHERE 1 == 1 ");
            if (!string.IsNullOrEmpty(((string[])searchModel.Title)[0]))
                sql.Append(" && o.Title LIKE '%" + ((string[])searchModel.Title)[0] + "%'");
            if (!string.IsNullOrEmpty(((string[])searchModel.Experience)[0]))
                sql.Append(" && o.ExperienceInYears = " + ((string[])searchModel.Experience)[0]);
            if (!string.IsNullOrEmpty(((string[])searchModel.Company)[0]))
                sql.Append(" && o.Company.Name LIKE '%" + ((string[])searchModel.Company)[0] + "%'");
            if (!string.IsNullOrEmpty(((string[])searchModel.Skills)[0]))
                sql.Append(" && (o.TechnicalSkills LIKE '%" + ((string[])searchModel.Skills)[0] + "%' || o.ManagerialSkills LIKE '%" + ((string[])searchModel.Skills)[0] + "%')");
            if (!string.IsNullOrEmpty(((string[])searchModel.SalaryFrom)[0]))
                sql.Append(" && o.SalaryFrom >= " + ((string[])searchModel.SalaryFrom)[0] + "");
            if (!string.IsNullOrEmpty(((string[])searchModel.SalaryTo)[0]))
                sql.Append(" && o.SalaryTo <= " + ((string[])searchModel.SalaryTo)[0] + "");

            ObjectQuery<Advertisement> objQuery = jassEntities.CreateQuery<Advertisement>(sql.ToString());
            IQueryable<Advertisement> searchResults = objQuery.Where(m => DateTime.Compare(m.StartDate, DateTime.Now) >= 0 && !m.IsDeleted && !m.IsWithDrawn && !m.IsFulFilled).AsQueryable<Advertisement>();

            return from adv in searchResults orderby adv.AdvertisementType.Cost descending select adv;
        }
    }
}
